// users CRUD

var mysql = require('./db');

// <editor-fold defaultstate="collapsed" desc="readlogin = function(req, res)">
exports.readlogin = function(req, res) {

    mysql.query(
        'SELECT id,username,passwd,admin,sales ' +
        'FROM exxact.system_users ' +
        'WHERE deleted=0 AND username=? AND passwd=?',
        [
            req.query.user,
            req.query.passwd
        ],
    function (err, rows) {
        if(err) {
            res.json({ success: false, msg: err.code }); // passwd=12345 = 827ccb0eea8a706c4c34a16891f84e7b
        }
        else {
            var result = eval(rows);
            //console.log('result=',result,' l=',result.length);
            if(result.length) {
                var user = result[0];
                res.json(
                    { success: true, user: {id: user.id, name: user.username, admin: user.admin, sales: user.sales} }
                );
            }
            else {
                res.json({ success: false, msg: 'Incorrect user or password.' });
            }
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="read = function(req, res)">
exports.read = function(req, res) {

    var search = '';
    if(req.query.search) {
        search = 'WHERE username LIKE "%' + req.query.search + '%" OR fullname LIKE "%' + req.query.search + '%"';
    }
    var sort = JSON.parse(req.query.sort)[0];

    mysql.query(
/*rows[0]*/ 'SELECT SQL_CALC_FOUND_ROWS id,username,admin,sales,date,fullname,email,phone,deleted ' +
            'FROM exxact.system_users ' + search +
            'ORDER BY ' + sort.property + ' ' + sort.direction + ' LIMIT ' + req.query.start + ',' + req.query.limit + ';' +
/*rows[1]*/ 'SELECT FOUND_ROWS() total;',
    function (err, rows) {
        if(err) {
            res.json({ success: false, crud: 'select users', err: err });
        }
        else {
            res.json({ success: true, data: rows[0], total: eval(rows[1])[0].total });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="add = function(req, res)">
exports.add = function(req, res) {

    var param = req.body;
    delete param.id;

    mysql.query('INSERT INTO exxact.system_users SET ?', param, function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'insert user', err: err });
        }
        else {
            result.id = result.insertId;
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="update = function(req, res)">
exports.update = function(req, res) {

    var param = req.body;
    var id = param.id;
    delete param.id;

    mysql.query('UPDATE exxact.system_users SET ? WHERE id=?', [param, id], function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'update', err: err });
        }
        else {
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="destroy = function(req, res)">
exports.destroy = function(req, res) {

    var param = req.body;

    mysql.query('DELETE FROM exxact.system_users WHERE id=?', param.id, function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'delete', err: err });
        }
        else {
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="CREATE TABLE exxact.system_users">
/*
CREATE TABLE exxact.system_users (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` text NOT NULL,
  `passwd` text NOT NULL,
  `admin` int(11) NOT NULL DEFAULT '0',
  `sales` tinyint(1) NOT NULL DEFAULT '0',
  `date` text NOT NULL,
  `fullname` text NOT NULL,
  `email` text,
  `phone` varchar(32) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1
*/

// change table
// ALTER TABLE system_users MODIFY COLUMN username CHAR(32) NOT NULL DEFAULT '';
// ALTER TABLE system_users ADD INDEX username(username(32));
// ALTER TABLE system_users MODIFY COLUMN passwd CHAR(32) NOT NULL DEFAULT '';
// ALTER TABLE system_users MODIFY COLUMN date VARCHAR(15) NOT NULL DEFAULT '';
// ALTER TABLE system_users MODIFY COLUMN fullname VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE system_users ADD INDEX fullname(fullname(40));
// ALTER TABLE system_users MODIFY COLUMN email VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE system_users MODIFY COLUMN phone VARCHAR(32) NOT NULL DEFAULT '';

/* new table
CREATE TABLE exxact.system_users (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` char(32) NOT NULL DEFAULT '',
  `passwd` char(32) NOT NULL DEFAULT '',
  `admin` int(11) NOT NULL DEFAULT '0',
  `sales` tinyint(1) NOT NULL DEFAULT '0',
  `date` varchar(15) NOT NULL DEFAULT '',
  `fullname` varchar(64) NOT NULL DEFAULT '',
  `email` varchar(64) NOT NULL DEFAULT '',
  `phone` varchar(32) NOT NULL DEFAULT '',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `username` (`username`)
  KEY `fullname` (`fullname`(40))
) ENGINE=MyISAM AUTO_INCREMENT=64 DEFAULT CHARSET=latin1
 */
// </editor-fold>
